﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Data.Common;
using System.Configuration;

namespace ISB_Data
{
    public class Data
    {
        //string connectionstring = @"Data Source=CORESONANT-PC;Initial Catalog=ISB_DB;User ID=sa; Password=P@ssw0rd;";
        //string connectionstring = @"Data Source=shrutibharath;Initial Catalog=SETTLE;User ID=sa; Password=sa123;";
        string connectionstring = ConfigurationManager.ConnectionStrings["ISBDB"].ConnectionString;
        //string connectionstring = @"Data Source=172.16.0.248;Initial Catalog=For Delivery;User ID=sa; Password=shiva73;";
        public string GetAmount(string InstId, string TxnId)
        {
            System.Data.SqlClient.SqlConnection scon = new SqlConnection(connectionstring);
            try
            {
                System.Data.SqlClient.SqlCommand scmd = new SqlCommand("GetTxnAmount", scon);
                scmd.CommandType = System.Data.CommandType.StoredProcedure;
                scmd.Parameters.Add(new SqlParameter("@inst_id", InstId));
                scmd.Parameters.Add(new SqlParameter("@TxnId", TxnId));
                scon.Open();
                string Amount = Convert.ToString(scmd.ExecuteScalar());
                scon.Close();
                return Amount;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public string GetAmountByTxn(string TxnId)
        {
            System.Data.SqlClient.SqlConnection scon = new SqlConnection(connectionstring);
            try
            {
                System.Data.SqlClient.SqlCommand scmd = new SqlCommand("GetTxnAmountByTxn", scon);
                scmd.CommandType = System.Data.CommandType.StoredProcedure;
                scmd.Parameters.Add(new SqlParameter("@TxnId", TxnId));
                scon.Open();
                string Amount = Convert.ToString(scmd.ExecuteScalar());
                scon.Close();
                return Amount;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataSet GetItems(string TxnId)
        {
            System.Data.SqlClient.SqlConnection scon = new SqlConnection(connectionstring);
            try
            {
                System.Data.SqlClient.SqlCommand scmd = new SqlCommand("USP_Get_Items_By_TxnId", scon);
                scmd.CommandType = System.Data.CommandType.StoredProcedure;
                scmd.Parameters.Add(new SqlParameter("@TxnId", TxnId));
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(scmd);
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public string GetLogin(string UserName, string Password)
        {
            System.Data.SqlClient.SqlConnection scon = new SqlConnection(connectionstring);
            try
            {
                System.Data.SqlClient.SqlCommand scmd = new SqlCommand("GetUser_Login", scon);
                scmd.CommandType = System.Data.CommandType.StoredProcedure;
                scmd.Parameters.Add(new SqlParameter("@UserName", UserName));
                scmd.Parameters.Add(new SqlParameter("@Password", Password));
                string strUserId = string.Empty;
                scon.Open();
                strUserId = Convert.ToString(scmd.ExecuteScalar());
                scon.Close();
                return strUserId;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool PerformHHTransaction(long TxnId, string PeopleId, string StewardId)
        {
            System.Data.SqlClient.SqlConnection scon = new SqlConnection(connectionstring);
            try
            {
                System.Data.SqlClient.SqlCommand scmd = new SqlCommand("Perform_HH_Transaction", scon);
                scmd.CommandType = System.Data.CommandType.StoredProcedure;
                scmd.Parameters.Add(new SqlParameter("@TxnID", TxnId));
                scmd.Parameters.Add(new SqlParameter("@StewardId", StewardId));
                if (PeopleId != string.Empty)
                    scmd.Parameters.Add(new SqlParameter("@PeopleId", Convert.ToInt64(PeopleId)));
                string strUserId = string.Empty;
                scon.Open();
                scmd.ExecuteNonQuery();
                scon.Close();
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public string Test()
        {
            System.Data.SqlClient.SqlConnection scon = new SqlConnection(connectionstring);
            try
            {
                System.Data.SqlClient.SqlCommand scmd = new SqlCommand("GetUser_Login", scon);
                scon.Open();
                scon.Close();
                return "true";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
    }
}
